import os
import sys
from datetime import datetime

import mysql.connector
import openpyxl
import pandas as pd
from mysql.connector import Error
from PyQt5.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QLabel,
    QLineEdit, QPushButton, QTableWidget, QTableWidgetItem, QHeaderView,
    QMessageBox, QDialog, QDialogButtonBox, QFormLayout, QTabWidget, QFileDialog
)
from PyQt5.QtGui import QFont, QIcon, QIntValidator
from PyQt5.QtCore import Qt
from openpyxl.styles import numbers

# 直接使用现有的CustomerManager类
from Manage.customer_manager import CustomerManager


class CustomerDialog(QDialog):
    """客户信息对话框 - 用于添加和编辑客户"""

    def __init__(self, customer=None, parent=None):
        print("数据库连接初始化")
        try:
            # 数据库连接配置
            self.connection = mysql.connector.connect(
                host='localhost',
                database='bank_system',
                user='root',
                password='1234'
            )

            if self.connection.is_connected():
                # 使用推荐的属性替代弃用的方法
                db_info = self.connection.server_info
                print(f"成功连接到MySQL服务器 (版本 {db_info})")
        except Error as e:
            print(f"数据库连接失败: {e}")
            # 显示错误消息框
            QMessageBox.critical(None, "数据库错误", f"无法连接数据库: {str(e)}")
            raise

        super().__init__(parent)
        self.customer = customer
        self.setWindowTitle("编辑客户" if customer else "添加新客户")
        self.setFixedSize(400, 300)

        # 创建表单控件
        self.name_input = QLineEdit()
        self.pid_input = QLineEdit()
        self.phone_input = QLineEdit()
        self.address_input = QLineEdit()

        # 设置输入限制
        self.pid_input.setMaxLength(18)  # 身份证号长度限制
        self.phone_input.setMaxLength(11)  # 手机号长度限制

        # 创建按钮
        self.button_box = QDialogButtonBox(QDialogButtonBox.Ok | QDialogButtonBox.Cancel)
        self.button_box.accepted.connect(self.validate_and_accept)
        self.button_box.rejected.connect(self.reject)

        # 设置布局
        layout = QFormLayout()
        layout.addRow("客户姓名:", self.name_input)
        layout.addRow("身份证号:", self.pid_input)
        layout.addRow("手机号:", self.phone_input)
        layout.addRow("地址:", self.address_input)
        layout.addRow(self.button_box)

        self.setLayout(layout)

        # 如果是编辑模式，填充现有数据
        if customer:
            self.populate_fields()

    def populate_fields(self):
        """填充现有客户数据"""
        self.name_input.setText(self.customer.get('customerName', ''))
        self.pid_input.setText(self.customer.get('PID', ''))
        self.phone_input.setText(self.customer.get('telephone', ''))
        self.address_input.setText(self.customer.get('address', ''))

    def validate_and_accept(self):
        """验证表单数据 - 增加电话号码唯一性检查"""
        # 获取输入值
        name = self.name_input.text().strip()
        pid = self.pid_input.text().strip()
        phone = self.phone_input.text().strip()

        # 基本验证
        if not name:
            QMessageBox.warning(self, "错误", "客户姓名不能为空")
            return
        if not pid:
            QMessageBox.warning(self, "错误", "身份证号不能为空")
            return
        if not phone:
            QMessageBox.warning(self, "错误", "手机号不能为空")
            return
        if len(pid) != 18:
            QMessageBox.warning(self, "错误", "身份证号必须为18位")
            return
        if len(phone) != 11 or not phone.isdigit():
            QMessageBox.warning(self, "错误", "请输入有效的11位手机号")
            return

        # 创建临时数据库连接用于验证
        try:
            connection = mysql.connector.connect(
                host='localhost',
                database='bank system',
                user='root',
                password='1234'
            )
            cursor = connection.cursor()

            # 检查电话号码是否已存在（排除当前编辑的客户）
            if self.customer:
                # 编辑模式：检查其他客户是否使用该电话号码
                query = "SELECT COUNT(*) FROM userinfo WHERE telephone = %s AND customerID != %s"
                cursor.execute(query, (phone, self.customer.get('customerID')))
            else:
                # 添加模式：检查是否有客户使用该电话号码
                query = "SELECT COUNT(*) FROM userinfo WHERE telephone = %s"
                cursor.execute(query, (phone,))

            count = cursor.fetchone()[0]
            if count > 0:
                QMessageBox.warning(self, "错误", "该手机号已被其他客户使用")
                return

        except Error as e:
            print(f"数据库验证失败: {e}")
            QMessageBox.critical(self, "错误", f"验证手机号唯一性时发生错误: {str(e)}")
            return
        finally:
            if cursor:
                cursor.close()
            if connection and connection.is_connected():
                connection.close()

        # 所有验证通过
        self.accept()

    def get_values(self):
        """获取表单数据"""
        return {
            'customername': self.name_input.text().strip(),
            'pid': self.pid_input.text().strip(),
            'telephone': self.phone_input.text().strip(),
            'address': self.address_input.text().strip() or None
        }


class AdminModule(QWidget):
    """客户管理主界面 - 增强版：增加ID范围查询功能"""

    COLUMNS = ["ID", "姓名", "身份证号", "手机号", "地址"]

    def __init__(self):
        super().__init__()
        self.manager = CustomerManager()  # 使用现有的CustomerManager
        self.setup_ui()
        self.load_customers()

        # 初始化ID范围过滤器是否启用标志 (修复错误)
        self.id_range_enabled = self.id_range_container.isVisible()

    def setup_ui(self):
        """初始化界面 - 增加ID范围查询区域"""
        layout = QVBoxLayout()

        # 操作按钮区域
        btn_layout = QHBoxLayout()

        self.add_btn = QPushButton("添加客户")
        self.add_btn.setIcon(QIcon(":/icons/add.png"))
        self.add_btn.clicked.connect(self.add_customer)

        self.edit_btn = QPushButton("编辑客户")
        self.edit_btn.setIcon(QIcon(":/icons/edit.png"))
        self.edit_btn.clicked.connect(self.edit_customer)

        self.delete_btn = QPushButton("删除客户")
        self.delete_btn.setIcon(QIcon(":/icons/delete.png"))
        self.delete_btn.clicked.connect(self.delete_customer)

        self.refresh_btn = QPushButton("刷新")
        self.refresh_btn.setIcon(QIcon(":/icons/refresh.png"))
        self.refresh_btn.clicked.connect(self.load_customers)

        # 新增ID范围查询按钮
        self.id_range_btn = QPushButton("ID范围查询")
        self.id_range_btn.setIcon(QIcon(":/icons/filter.png"))
        self.id_range_btn.clicked.connect(self.toggle_id_range_filter)

        # 新增导出报表按钮
        self.export_btn = QPushButton("导出报表")
        self.export_btn.setIcon(QIcon.fromTheme("document-export"))
        self.export_btn.setToolTip("导出当前客户数据为报表")
        self.refresh_btn = QPushButton("刷新")

        btn_layout.addWidget(self.add_btn)
        btn_layout.addWidget(self.edit_btn)
        btn_layout.addWidget(self.delete_btn)
        btn_layout.addStretch()
        btn_layout.addWidget(self.export_btn)  # 添加导出按钮
        btn_layout.addWidget(self.id_range_btn)
        btn_layout.addWidget(self.refresh_btn)

        # 搜索区域
        search_layout = QHBoxLayout()
        self.search_input = QLineEdit()
        self.search_input.setPlaceholderText("输入姓名、身份证号或手机号搜索...")
        self.search_input.textChanged.connect(self.filter_customers)
        search_layout.addWidget(QLabel("搜索:"))
        search_layout.addWidget(self.search_input)

        # 创建ID范围查询容器
        self.id_range_container = QWidget()
        id_range_layout = QHBoxLayout(self.id_range_container)

        id_range_label = QLabel("客户ID范围:")
        self.id_min_input = QLineEdit()
        self.id_min_input.setPlaceholderText("起始ID")
        self.id_min_input.setValidator(QIntValidator())

        id_to_label = QLabel(" 到 ")

        self.id_max_input = QLineEdit()
        self.id_max_input.setPlaceholderText("结束ID")
        self.id_max_input.setValidator(QIntValidator())

        apply_range_btn = QPushButton("应用")
        apply_range_btn.clicked.connect(self.apply_id_range_filter)

        clear_range_btn = QPushButton("清除")
        clear_range_btn.clicked.connect(self.clear_id_range_filter)

        id_range_layout.addWidget(id_range_label)
        id_range_layout.addWidget(self.id_min_input)
        id_range_layout.addWidget(id_to_label)
        id_range_layout.addWidget(self.id_max_input)
        id_range_layout.addWidget(apply_range_btn)
        id_range_layout.addWidget(clear_range_btn)

        # 默认隐藏ID范围区域
        self.id_range_container.setVisible(False)
        # 连接信号
        self.export_btn.clicked.connect(self.export_report)

        # 客户表格
        self.table = QTableWidget()
        self.table.setColumnCount(len(self.COLUMNS))
        self.table.setHorizontalHeaderLabels(self.COLUMNS)
        self.table.setSelectionBehavior(QTableWidget.SelectRows)
        self.table.setSelectionMode(QTableWidget.SingleSelection)
        self.table.setEditTriggers(QTableWidget.NoEditTriggers)

        # 设置列宽策略
        header = self.table.horizontalHeader()
        header.setSectionResizeMode(0, QHeaderView.ResizeToContents)  # ID列
        header.setSectionResizeMode(1, QHeaderView.Stretch)  # 姓名列
        header.setSectionResizeMode(2, QHeaderView.ResizeToContents)  # 身份证号
        header.setSectionResizeMode(3, QHeaderView.ResizeToContents)  # 手机号
        header.setSectionResizeMode(4, QHeaderView.Stretch)  # 地址

        # 组装布局
        layout.addLayout(btn_layout)
        layout.addLayout(search_layout)
        layout.addWidget(self.id_range_container)  # 添加ID范围容器
        layout.addWidget(self.table)

        self.setLayout(layout)

    def toggle_id_range_filter(self):
        """切换ID范围查询区域的显示状态"""
        # 直接使用容器的可见状态，不需要单独的变量
        visible = not self.id_range_container.isVisible()
        self.id_range_container.setVisible(visible)

        # 如果禁用了范围查询，刷新数据
        if not visible:
            self.filter_customers()

    def apply_id_range_filter(self):
        """应用ID范围过滤器"""
        self.filter_customers()

    def clear_id_range_filter(self):
        """清除ID范围过滤条件"""
        self.id_min_input.clear()
        self.id_max_input.clear()
        self.filter_customers()

    def load_customers(self):
        """加载客户列表 - 增加ID范围处理"""
        try:
            customers = self.manager.list_all_customers()
            self.original_data = customers  # 保存原始数据用于过滤

            # 如果启用了ID范围过滤，先应用过滤
            if self.id_range_container.isVisible():
                min_id = None
                max_id = None
                try:
                    if self.id_min_input.text().strip():
                        min_id = int(self.id_min_input.text().strip())
                    if self.id_max_input.text().strip():
                        max_id = int(self.id_max_input.text().strip())
                except ValueError:
                    pass  # 忽略无效输入

                if min_id is not None or max_id is not None:
                    customers = [
                        c for c in customers
                        if (min_id is None or c.get('customerID', 0) >= min_id) and
                           (max_id is None or c.get('customerID', 0) <= max_id)
                    ]

            self.table.setRowCount(len(customers))

            for row, customer in enumerate(customers):
                items = [
                    str(customer.get('customerID', '')),
                    customer.get('customerName', ''),
                    customer.get('PID', ''),
                    customer.get('telephone', ''),
                    customer.get('address', ''),
                    customer.get('registerDate', '').strftime('%Y-%m-%d') if customer.get('registerDate') else ''
                ]

                for col, value in enumerate(items):
                    item = QTableWidgetItem(value)
                    item.setTextAlignment(Qt.AlignCenter)
                    self.table.setItem(row, col, item)

            # 更新按钮状态
            has_data = len(customers) > 0
            self.edit_btn.setEnabled(has_data)
            self.delete_btn.setEnabled(has_data)

        except Exception as e:
            QMessageBox.critical(self, "错误", f"加载客户数据失败: {str(e)}")

    def filter_customers(self):
        """过滤客户列表 - 增加ID范围支持"""
        try:
            # 安全获取搜索文本
            search_text = self.search_input.text().strip()
            if search_text:
                search_text = search_text.lower()

            # 获取ID范围值
            min_id = None
            max_id = None
            if self.id_range_container.isVisible():  # 使用容器可见状态代替变量
                try:
                    if self.id_min_input.text().strip():
                        min_id = int(self.id_min_input.text().strip())
                    if self.id_max_input.text().strip():
                        max_id = int(self.id_max_input.text().strip())
                except ValueError:
                    # 无效输入，忽略范围过滤
                    pass

            # 如果没有搜索文本和ID范围，则加载全部客户数据
            if not search_text and min_id is None and max_id is None:
                self.load_customers()
                return

            # 安全检查原始数据
            if not hasattr(self, 'original_data') or not self.original_data:
                self.table.setRowCount(0)
                return

            filtered = []

            # 安全处理每个客户数据
            for c in self.original_data:
                try:
                    # 安全获取字段值
                    customer_id = c.get('customerID', 0)
                    name = str(c.get('customerName', '') or '')
                    pid = str(c.get('PID', '') or '')
                    phone = str(c.get('telephone', '') or '')
                    address = str(c.get('address', '') or '')

                    # 检查ID范围
                    if min_id is not None and customer_id < min_id:
                        continue
                    if max_id is not None and customer_id > max_id:
                        continue

                    # 如果没有搜索文本，直接添加
                    if not search_text:
                        filtered.append({
                            'customerID': customer_id,
                            'customerName': name,
                            'PID': pid,
                            'telephone': phone,
                            'address': address,
                            'registerDate': c.get('registerDate')
                        })
                        continue

                    # 将所有值转换为小写进行匹配
                    name_lower = name.lower()
                    pid_lower = pid.lower()
                    phone_lower = phone.lower()
                    address_lower = address.lower()

                    # 检查是否匹配任意字段
                    if (search_text in name_lower or
                            search_text in pid_lower or
                            search_text in phone_lower or
                            search_text in address_lower):
                        filtered.append({
                            'customerID': customer_id,
                            'customerName': name,
                            'PID': pid,
                            'telephone': phone,
                            'address': address,
                            'registerDate': c.get('registerDate')
                        })

                except Exception as e:
                    # 记录错误并继续
                    print(f"处理客户数据时出错: {str(e)}")
                    continue

            # 安全设置行数
            self.table.setRowCount(len(filtered))

            # 安全填充表格
            for row, customer in enumerate(filtered):
                try:
                    # 安全获取字段值
                    cust_id = str(customer.get('customerID', ''))
                    name = customer.get('customerName', '')
                    pid = customer.get('PID', '')
                    phone = customer.get('telephone', '')
                    address = customer.get('address', '')
                    reg_date = customer.get('registerDate')
                    date_str = reg_date.strftime('%Y-%m-%d') if reg_date else ''

                    # 构建项目列表
                    items = [cust_id, name, pid, phone, address, date_str]

                    # 填充表格行
                    for col, value in enumerate(items):
                        # 使用纯文本安全填充所有字段
                        item = QTableWidgetItem(value)
                        item.setTextAlignment(Qt.AlignCenter)
                        self.table.setItem(row, col, item)

                except Exception as e:
                    # 处理错误行
                    print(f"填充表格行时出错: {str(e)}")
                    for col in range(len(items)):
                        item = QTableWidgetItem("错误")
                        item.setTextAlignment(Qt.AlignCenter)
                        self.table.setItem(row, col, item)

        except Exception as main_e:
            # 处理主循环错误
            QMessageBox.critical(self, "错误", f"过滤客户时发生严重错误: {str(main_e)}")
            self.table.setRowCount(0)

    def get_selected_customer_id(self):
        """获取选中客户的ID"""
        row = self.table.currentRow()
        if row < 0:
            return None
        item = self.table.item(row, 0)
        return int(item.text()) if item and item.text().isdigit() else None

    def check_phone_unique(self, phone, exclude_id=None):
        """
        安全检查电话号码是否唯一
        :param phone: 要检查的电话号码
        :param exclude_id: 要排除的客户ID（用于编辑时排除自己）
        :return: 如果唯一返回True，否则返回False，错误时也返回False
        """
        try:
            # 使用一个新的数据库连接以避免冲突
            connection = mysql.connector.connect(
                host='localhost',
                database='bank system',
                user='root',
                password='1234'
            )
            cursor = connection.cursor()

            if exclude_id:
                query = "SELECT COUNT(*) FROM userinfo WHERE telephone = %s AND customerID != %s"
                cursor.execute(query, (phone, exclude_id))
            else:
                query = "SELECT COUNT(*) FROM userinfo WHERE telephone = %s"
                cursor.execute(query, (phone,))

            count = cursor.fetchone()[0]
            return count == 0

        except Exception as e:
            print(f"检查电话号码唯一性失败: {e}")
            QMessageBox.warning(self, "错误", f"检查电话号码时发生错误: {str(e)}")
            return False
        finally:
            # 确保关闭连接
            if 'cursor' in locals() and cursor:
                cursor.close()
            if 'connection' in locals() and connection and connection.is_connected():
                connection.close()

    def add_customer(self):
        """添加新客户 - 安全版本"""
        dialog = CustomerDialog()
        if dialog.exec_() == QDialog.Accepted:
            data = dialog.get_values()

            # 检查电话号码唯一性 - 使用安全方法
            if not self.check_phone_unique(data['telephone']):
                # 错误信息已经在上面的方法中显示，这里只返回
                return

            try:
                if self.manager.add_customer(**data):
                    QMessageBox.information(self, "成功", "客户添加成功")
                    self.load_customers()
                else:
                    QMessageBox.warning(self, "失败", "添加客户失败")
            except Exception as e:
                QMessageBox.critical(self, "错误", f"添加客户时发生系统错误: {str(e)}")

    def edit_customer(self):
        """编辑客户信息 - 安全版本"""
        customer_id = self.get_selected_customer_id()
        if not customer_id:
            QMessageBox.warning(self, "警告", "请先选择要编辑的客户")
            return

        try:
            customer = self.manager.get_customer_by_id(customer_id)
            if not customer:
                QMessageBox.warning(self, "错误", "找不到指定的客户")
                return

            dialog = CustomerDialog(customer)
            if dialog.exec_() == QDialog.Accepted:
                data = dialog.get_values()

                # 检查电话号码唯一性（排除当前客户）
                if not self.check_phone_unique(data['telephone'], exclude_id=customer_id):
                    # 错误信息已经在上面的方法中显示
                    return

                if self.manager.update_customer(
                        customer_id,
                        customername=data['customername'],
                        pid=data['pid'],
                        telephone=data['telephone'],
                        address=data['address']
                ):
                    QMessageBox.information(self, "成功", "客户信息更新成功")
                    self.load_customers()
                else:
                    QMessageBox.warning(self, "失败", "更新客户信息失败")

        except Exception as e:
            QMessageBox.critical(self, "错误", f"编辑客户时发生系统错误: {str(e)}")

    def delete_customer(self):
        """删除客户"""
        customer_id = self.get_selected_customer_id()
        if not customer_id:
            QMessageBox.warning(self, "警告", "请先选择要删除的客户")
            return

        reply = QMessageBox.question(
            self,
            "确认删除",
            "确定要删除此客户吗？此操作不可恢复！",
            QMessageBox.Yes | QMessageBox.No
        )

        if reply == QMessageBox.Yes:
            if self.manager.delete_customer(customer_id):
                QMessageBox.information(self, "成功", "客户删除成功")
                self.load_customers()
            else:
                QMessageBox.warning(self, "失败", "删除客户失败，可能有关联银行卡")

    def export_report(self):
        """导出客户报表到Excel文件"""
        try:
            # 获取当前所有客户数据
            all_customers = self.get_current_customers()

            if not all_customers:
                QMessageBox.warning(self, "导出失败", "没有客户数据可供导出")
                return

            # 创建数据框
            df = pd.DataFrame(all_customers)

            # 重命名列（中文表头）
            column_mapping = {
                'customerID': '客户ID',
                'customerName': '客户姓名',
                'PID': '身份证号',
                'telephone': '手机号码',
                'address': '联系地址'
            }
            df = df.rename(columns=column_mapping)

            # 设置默认文件名
            current_date = datetime.now().strftime("%Y%m%d")
            default_filename = f"客户报表_{current_date}.xlsx"

            # 让用户选择保存位置
            file_path, _ = QFileDialog.getSaveFileName(
                self,
                "导出客户报表",
                os.path.join(os.path.expanduser("~"), "Downloads", default_filename),
                "Excel 文件 (*.xlsx);;CSV 文件 (*.csv);;所有文件 (*)"
            )

            if not file_path:
                return  # 用户取消了导出

            # 根据文件扩展名决定导出格式
            if file_path.lower().endswith('.csv'):
                # 导出为CSV
                df.to_csv(file_path, index=False, encoding='utf-8-sig')
                export_format = "CSV"
            else:
                # 确保扩展名为xlsx
                if not file_path.lower().endswith('.xlsx'):
                    file_path += '.xlsx'

                # 导出为Excel（带格式）
                self.export_to_excel(df, file_path)
                export_format = "Excel"

            # 显示成功消息
            QMessageBox.information(
                self,
                "导出成功",
                f"客户报表已成功导出为{export_format}文件！\n\n保存位置: {file_path}"
            )

        except Exception as e:
            QMessageBox.critical(
                self,
                "导出失败",
                f"导出报表时发生错误:\n{str(e)}\n\n请确保Excel软件未被占用"
            )

    def get_current_customers(self):
        """
        使用SELECT语句从数据库获取当前显示的客户数据
        （包括任何搜索或筛选条件的结果）
        """
        try:
            # 确定当前的搜索条件
            search_conditions = {}

            # 检查基本搜索
            if hasattr(self, 'search_input') and self.search_input.text().strip():
                keyword = self.search_input.text().strip()
                search_conditions = {
                    'type': 'basic',
                    'keyword': keyword
                }

            # 检查高级搜索
            elif hasattr(self, 'current_search_params') and self.current_search_params:
                search_conditions = {
                    'type': 'advanced',
                    'params': self.current_search_params
                }

            # 如果没有搜索条件，获取所有客户
            if not search_conditions:
                return self.manager.list_all_customers()

            # 根据搜索条件类型执行相应查询
            if search_conditions['type'] == 'basic':
                return self.execute_basic_search(search_conditions['keyword'])
            else:  # advanced
                return self.execute_advanced_search(search_conditions['params'])

        except Exception as e:
            # 错误处理
            print(f"获取当前客户数据失败: {str(e)}")
            # 回退到从表格提取数据
            return self.get_customers_from_table()

    def execute_basic_search(self, keyword):
        """执行基本搜索 - 使用SELECT语句"""
        query = """
        SELECT customerID, customerName, PID, telephone, address
        FROM userInfo
        WHERE customerName LIKE %s
            OR PID LIKE %s
            OR telephone LIKE %s
            OR address LIKE %s
        ORDER BY customerID
        """
        params = (
            f"%{keyword}%", f"%{keyword}%",
            f"%{keyword}%", f"%{keyword}%"
        )
        return self.manager.db.execute_query(query, params)

    def execute_advanced_search(self, params):
        """执行高级搜索 - 使用SELECT语句"""
        # 构建查询语句
        query = "SELECT customerID, customerName, PID, telephone, address FROM userInfo WHERE 1=1"
        query_params = []

        # 添加条件
        if 'customerID' in params and params['customerID']:
            query += " AND customerID = %s"
            query_params.append(params['customerID'])

        if 'customerName' in params and params['customerName']:
            query += " AND customerName LIKE %s"
            query_params.append(f"%{params['customerName']}%")

        if 'PID' in params and params['PID']:
            query += " AND PID LIKE %s"
            query_params.append(f"%{params['PID']}%")

        if 'telephone' in params and params['telephone']:
            query += " AND telephone LIKE %s"
            query_params.append(f"%{params['telephone']}%")

        if 'address' in params and params['address']:
            query += " AND address LIKE %s"
            query_params.append(f"%{params['address']}%")

        # 添加排序
        query += " ORDER BY customerID"

        return self.customer_manager.db.execute_query(query, query_params)

    def export_to_excel(self, df, file_path):
        """将DataFrame导出为格式化的Excel文件（兼容新版本openpyxl）"""
        # 创建Excel写入器
        writer = pd.ExcelWriter(file_path, engine='openpyxl')
        df.to_excel(writer, index=False, sheet_name='客户列表')

        # 获取Excel工作表对象
        workbook = writer.book
        worksheet = writer.sheets['客户列表']

        # 设置列宽
        column_widths = {
            '客户ID': 10,
            '客户姓名': 15,
            '身份证号': 25,
            '手机号码': 18,
            '联系地址': 35
        }

        # 修改点1：使用工作表维度设置而不是逐个单元格
        for col_letter, col_name in zip('ABCDE', df.columns):
            col_dim = worksheet.column_dimensions[col_letter]
            col_dim.width = column_widths.get(col_name, 12)

        # 设置标题行格式
        header_fill = openpyxl.styles.PatternFill(
            start_color="1F497D",
            end_color="1F497D",
            fill_type="solid"
        )
        header_font = openpyxl.styles.Font(
            color="FFFFFF",
            bold=True
        )
        header_alignment = openpyxl.styles.Alignment(
            horizontal="center",
            vertical="center"
        )

        # 修改点2：使用工作表标题行访问
        header_row = worksheet[1]
        for cell in header_row:
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = header_alignment

        # 设置数据行格式
        alignment = openpyxl.styles.Alignment(
            vertical="center"
        )

        # 修改点3：使用更简洁的方式设置整列格式
        # 设置手机号列为文本格式（避免科学计数法问题）
        text_format = '@'  # 文本格式
        for row in worksheet.iter_rows(min_row=2, min_col=4, max_col=4):
            for cell in row:
                cell.number_format = text_format

        # 设置所有单元格垂直居中
        for row in worksheet.iter_rows(min_row=2):
            for cell in row:
                cell.alignment = alignment

        # 添加报表标题和导出时间
        worksheet.insert_rows(1)
        title_cell = worksheet.cell(row=1, column=1)
        title_cell.value = "银行客户管理系统 - 客户报表"
        title_cell.font = openpyxl.styles.Font(
            bold=True,
            size=14
        )
        title_cell.alignment = openpyxl.styles.Alignment(horizontal="center")

        # 合并标题单元格
        worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)

        # 添加导出时间
        export_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        time_row = worksheet.max_row + 1
        worksheet.cell(row=time_row, column=1).value = f"导出时间: {export_time}"
        worksheet.cell(row=time_row, column=1).font = openpyxl.styles.Font(
            italic=True,
            color="808080"
        )

        # 合并时间单元格
        worksheet.merge_cells(start_row=time_row, start_column=1, end_row=time_row, end_column=5)

        # 保存Excel文件
        workbook.save(file_path)


class AdminDashboard(QMainWindow):
    """管理员主窗口"""

    def __init__(self):
        super().__init__()
        self.setWindowTitle("银行系统 - 管理员面板")
        self.setMinimumSize(1024, 768)

        # 创建标签页
        tab_widget = QTabWidget()

        # 添加客户管理模块
        self.customer_module = AdminModule()
        tab_widget.addTab(self.customer_module, "客户管理")

        # 可以添加其他模块...
        # tab_widget.addTab(OtherModule(), "其他管理")

        self.setCentralWidget(tab_widget)

        # 状态栏
        self.statusBar().showMessage("就绪")


if __name__ == "__main__":
    app = QApplication(sys.argv)

    # 设置全局字体
    font = QFont()
    font.setFamily("Microsoft YaHei" if sys.platform == 'win32' else "Arial")
    font.setPointSize(10)
    app.setFont(font)

    window = AdminDashboard()
    window.show()

    sys.exit(app.exec_())